using System;
using System.IO;
using System.Text;
using System.Reflection;
using System.Collections.Generic;
using Crainiate.Data.Persistence.Cache;
using Crainiate.Data.Persistence.Providers.Labelling;

namespace Crainiate.Data.Persistence.Providers.SqlServer
{
    public sealed class SqlCommand : Command
    {
        //Structures
        private struct QueryInformation
        {
            public string LastKey;
            public Dictionary<string, bool> Keys;
            public IRelationship LastRelationship;
            public ReflectionCacheItem Cache;
        }

        //Property variables
        private Context _context;
        private Formatter _formatter;
        private StringBuilder _builder;
        private System.Data.SqlClient.SqlDataReader _reader;

        #region Interface

        //Constructors
        public SqlCommand(SqlContext context)
        {
            Context = context;
        }

        //Properties
        public override Context Context
        {
            get
            {
                return _context;
            }
            set
            {
                _context = value;
            }
        }

        public Formatter Formatter
        {
            get
            {
                if (_formatter == null)
                {
                    if (Context == null) throw new CommandException("Formatter cannot be created as the Context has not been set for this Command object.");
                    _formatter = Context.CreateFormatter();
                    _formatter.Command = this;
                }
                return _formatter;
            }
            set
            {
                if (value.GetType() != typeof(SqlFormatter)) throw new CommandException("Formatter must be of type SqlFormatter");
                _formatter = value;
                _formatter.Command = this;
            }
        }

        public StringBuilder Builder
        {
            get
            {
                return _builder;
            }
        }

        public System.Data.SqlClient.SqlDataReader Reader
        {
            get
            {
                return _reader;
            }
        }

        //Methods
        public override void Select(IPersistable graph)
        {
            GetStorage(graph);
        }

        public override void Execute(IQuery query)
        {
            GetStorage(query);
        }

        public override void Update(IPersistable graph)
        {
            UpdateStorage(graph);
        }

        public override void Insert(IPersistable graph)
        {
            CreateStorage(graph);
        }

        public override void Delete(IPersistable graph)
        {
            DeleteStorage(graph);
        }

        public override void Promote(IPersistable graph)
        {
            PromoteStorage(graph);
        }

        public override void Demote(IPersistable graph)
        {
            DemoteStorage(graph);
        }

        //One record exists
        public override bool Exists(IPersistable graph)
        {
            return GetStorageExists(graph);
        }

        //Reset
        public override void Reset()
        {
            _context = null;
            _builder = null;
            _formatter = null;
            _reader = null;
        }

        #endregion

        #region Implementation

        //Returns storage for an IPersistable graph
        private void GetStorage(IPersistable graph)
        {
            Type type = graph.GetType();

            //Reset concurrency
            if (graph.Concurrency != null) graph.Concurrency.Status = ConcurrencyStatus.None;

            //Get the stored procedure name and set up the context
            SqlContext context = (SqlContext)_context;

            //Set the context
            _builder = new StringBuilder();
            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(type, ProcedureType.Select));
            _builder.Append("] ");

            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Add the key values as parameters
            bool flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) _builder.Append(",");

                _builder.Append("@");
                _builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                _builder.Append("=");
                SqlFormatter.EncodeProperty(_builder, prop, prop.GetValue(graph, null));

                flag = true;
            }

            //Open a connection and execute the procedure
            System.Data.SqlClient.SqlConnection conn = context.GetConnection();
            System.Data.SqlClient.SqlCommand command = conn.CreateCommand();
            command.CommandText = _builder.ToString();

            try
            {
                _reader = command.ExecuteReader(System.Data.CommandBehavior.KeyInfo);

                if (!_reader.HasRows) throw new RecordNotFoundException("A record was not found for the " + type.ToString() + " object provided.");

                _reader.Read();

                SqlFormatter formatter = (SqlFormatter)Formatter;
                formatter.Reader = _reader; //Set the reader in the formatter
                
                formatter.Concurrency = graph.Concurrency;
                if (formatter.Concurrency != null) formatter.Concurrency.Reset();

                formatter.WithConcurrency = false;
                formatter.WithInheritance = true;
                formatter.WithKeys = false;
                formatter.WithIdentity = false;
                formatter.Prefix = context.Prefix;

                formatter.Decode(graph, type);
                formatter.Reset(); //Remove any object property references
            }
            catch (RecordNotFoundException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw new CommandException("An error occured getting storage for type " + type.ToString() + ".", ex);
            }
            finally
            {
                //Clean up data objects
                if (_reader != null) _reader.Dispose();
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }
        }

        private void GetStorage(IQuery query)
        {
            //Get the stored procedure name and set up the context
            SqlContext context = (SqlContext)_context;

            //Set up the builder
            _builder = new StringBuilder();
            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(query));
            _builder.Append("] ");

            //Add the values from the conditions to the procedure
            SqlFormatter.AppendQueryParameterValues(_builder, query, new List<string>());

            //Open a connection and execute the procedure
            System.Data.SqlClient.SqlConnection conn = context.GetConnection();
            System.Data.SqlClient.SqlCommand command = conn.CreateCommand();
            command.CommandText = _builder.ToString();

            try
            {
                _reader = command.ExecuteReader(System.Data.CommandBehavior.KeyInfo);

                //Set up the formatter
                SqlFormatter formatter = (SqlFormatter)Formatter;
                formatter.Reader = _reader; //Set the reader in the formatter

                formatter.WithConcurrency = false;
                formatter.WithInheritance = true;
                formatter.WithKeys = true;
                formatter.WithIdentity = false;
                formatter.Prefix = context.Prefix;

                //Return results depending on whether there are any subqueries that need filling
                if (IsComplexQuery(query))
                {
                    GetStorageResults(query, formatter);
                }
                else
                {
                    GetSingleQueryResults(query, formatter);
                }
            }
            catch (Exception ex)
            {
                throw new CommandException("An error occurred getting storage for query type " + query.Type.ToString() + ".", ex);
            }
            finally
            {
                if (_reader != null) _reader.Dispose();
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }
        }

        //Get the storage results for a query with no child queries requiring results
        private void GetSingleQueryResults(IQuery query, SqlFormatter formatter)
        {
            formatter.Ordinals = new Ordinals(query);
            formatter.Label = new TypeLabelHash(query.Type, new QueryLabelHash(query));

            query = FindQuery(query); //Finds the query to be filled

            //Iterate through the reader
            while (_reader.Read())
            {
                //Create a new peristable object
                IPersistable newObject = Context.CreatePersistable(query.ActivateType);

                //Set the concurrency
                newObject.Concurrency = Context.CreateConcurrency();
                formatter.Concurrency = newObject.Concurrency;
                
                formatter.Decode(newObject, query.ActivateType);
                query.Results.Add(newObject);
            }

            formatter.Reset(); //Remove any object property references from the formatter
        }

        //Create a dictionary of keys used and the last key value
        //Use the dictionary to determine if a new object should be added to the results
        //Set up the relationships collection
        private void GetStorageResults(IQuery query, SqlFormatter formatter)
        {
            //Set up the query dictionaries through recursion
            Dictionary<IQuery, QueryInformation> dictionary = new Dictionary<IQuery, QueryInformation>();
            AddQueryToDictionary(query, dictionary);

            //Set up the relationships object for all filled results
            SetRelationships(query, new Relationships());

            //Set the ordinals and parent label
            formatter.Ordinals = new Ordinals(query);
            formatter.Label = new QueryLabelHash(query);

            while (_reader.Read())
            {
                GetStorageResult(query, formatter, dictionary, null);
            }

            formatter.Reset(); //Remove any object property references from the formatter
        }

        private void GetStorageResult(IQuery query, SqlFormatter formatter, Dictionary<IQuery, QueryInformation> dictionary, IRelationship parent)
        {
            //Key the query info entry for this query
            QueryInformation info = dictionary[query];
            LabelHash queryHash = formatter.Label;
            formatter.Label = new TypeLabelHash(query.Type, formatter.Label);

            //Get the key values as a string for the current row
            string tableName = Common.GetTypeName(query.ActivateType, formatter.Prefix);
            string keyValue = "";
            PropertyInfo[] keys = info.Cache.GetKeyProperties();

            //Create a string concatenation of the key values
            foreach (PropertyInfo prop in keys)
            {
                string name = Formatter.GetPropertyName(prop, ".");
                int ordinal = formatter.GetOrdinal(tableName, name, formatter.Ordinals[formatter.Label]);

                //Get value
                if (prop.PropertyType.IsEnum)
                {
                    keyValue += _reader.GetInt32(ordinal).ToString();
                }
                else
                {
                    keyValue += _reader.GetValue(ordinal);
                }
            }

            //Check to see if object has already been created
            if (info.LastKey != keyValue && !info.Keys.ContainsKey(keyValue))
            {
                //Create a new peristable object
                IPersistable newObject = Context.CreatePersistable(query.ActivateType);

                //Set the concurrency
                newObject.Concurrency = Context.CreateConcurrency();
                formatter.Concurrency = newObject.Concurrency;

                formatter.Decode(newObject, query.ActivateType);

                //Add to results
                query.Results.Add(newObject);

                //Create new relationship
                IRelationship relationship = new Relationship(newObject);

                //If GetStorage has been called by a parent, set the parent and child relationships
                if (parent != null)
                {
                    relationship.Parents.Add(parent.Result);
                    parent.Children.Add(newObject);
                }

                query.Results.Relationships.Add(newObject, relationship);

                info.LastKey = keyValue;
                info.Keys.Add(keyValue, false);
                info.LastRelationship = relationship;
            }

            //Recurse for each sub query
            foreach (IQuery subquery in query.SubQueries)
            {
                formatter.Label = new QueryLabelHash(subquery, queryHash); //label based on parent query label
                GetStorageResult(subquery, formatter, dictionary, info.LastRelationship);
            }

            formatter.Label = queryHash;
        }

        private void CreateStorage(IPersistable graph)
        {
            Type type = Common.GetPersistType(graph);
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Get a reference to the correct context and formatter classes
            SqlContext context = (SqlContext) _context;
            SqlFormatter formatter = (SqlFormatter) Formatter;

            //Format an insert
            formatter.WithConcurrency = false;
            formatter.WithInheritance = false;
            formatter.WithKeys = false;
            formatter.WithIdentity = false;
            formatter.Output = false;

            _builder = new StringBuilder();
            CreateStorageChildren(graph, type, context, formatter);

            if (cache.IsIdentity) _builder.Append("SELECT @_output \r\n");

            //Hold the returned identity value
            object identity = null;

            //Open up a transacted connection
            System.Data.SqlClient.SqlConnection conn = null;
            System.Data.SqlClient.SqlCommand command = null;
            System.Data.SqlClient.SqlTransaction transaction = null;

            try
            {
                conn = context.GetConnection();

                //Begin transaction if multiple updates
                if (!cache.IsUnderlying) transaction = conn.BeginTransaction();

                command = conn.CreateCommand();
                command.Transaction = transaction;

                command.CommandText = _builder.ToString();

                identity = command.ExecuteScalar();

                //Recover identity value
                if (cache.IsIdentity && identity != null)
                {
                    //Get value
                    if (cache.PrimaryKey.PropertyType.IsEnum)
                    {
                        cache.PrimaryKey.SetValue(graph, Enum.Parse(cache.PrimaryKey.PropertyType, identity.ToString()), null);
                    }
                    else
                    {
                        cache.PrimaryKey.SetValue(graph, identity, null);
                    }
                }

                //Update streams
                if (cache.HasBinaryProperties) UpdateBinaryStorage(command, graph, type, formatter);

                if (transaction != null) transaction.Commit();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                if (transaction != null) transaction.Rollback();
                
                //Throw the exception if not a duplicate key error
                if (ex.Number != 2627) throw ex; 
                
                throw new DuplicateKeyException("A record already exists for the key defined for type " + type.ToString());
            }
            catch (Exception ex)
            {
                if (transaction != null) transaction.Rollback();
                throw new Exception("An error occurred inserting an object of type " + type.ToString() + ".", ex);
            }
            finally
            {
                if (transaction != null) transaction.Dispose();
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }

            //Set the correct concurrency status
            if (graph.Concurrency != null) graph.Concurrency.Status = ConcurrencyStatus.Created;
        }

        private void CreateStorageChildren(IPersistable graph, Type type, SqlContext context, SqlFormatter formatter)
        {
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Recurse to the bottom of the inheritance chain
            if (!cache.IsUnderlying) CreateStorageChildren(graph, cache.BaseType, context, formatter);

            //Declare identity parameter if required
            if (cache.IsUnderlying && cache.IsIdentity)
            {
                _builder.Append("DECLARE @_output ");
                _builder.Append(SqlCommon.SqlFromProperty(cache.PrimaryKey));
                _builder.Append(" \r\n");
            }

            //Build the call to the stored proc
            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(type, ProcedureType.Insert));
            _builder.Append("] ");
            
            //Make sure the primary key is encoded, and with the output variable if an identity
            if (!cache.IsUnderlying) 
            {
                formatter.WithIdentity = true;
                if (cache.IsIdentity) formatter.Output = true;
            }

            formatter.Encode(graph, type);

            if (cache.IsUnderlying && cache.IsIdentity) _builder.Append(",@_identity=@_output OUTPUT");
            _builder.Append(" \r\n");
        }

        private void PromoteStorage(IPersistable graph)
        {
            Type type = Common.GetPersistType(graph);
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Get a reference to the correct context and formatter classes
            SqlContext context = (SqlContext)_context;
            SqlFormatter formatter = (SqlFormatter)Formatter;

            //Format an insert
            formatter.WithConcurrency = false;
            formatter.WithInheritance = false;
            formatter.WithKeys = false;
            formatter.WithIdentity = false;
            formatter.Output = false;

            _builder = new StringBuilder();

            //Build the call to the stored proc
            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(type, ProcedureType.Insert));
            _builder.Append("] ");

            Formatter.Encode(graph, type);

            //Open up a connection
            System.Data.SqlClient.SqlConnection conn = null;
            System.Data.SqlClient.SqlCommand command = null;

            try
            {
                conn = context.GetConnection();
                command = conn.CreateCommand();
                command.CommandText = _builder.ToString();
                command.ExecuteNonQuery();

                //Update streams
                if (cache.HasBinaryProperties) UpdateBinaryStorage(command, graph, type, formatter);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                //Throw the exception if not a duplicate key error
                if (ex.Number != 2627) throw ex;

                throw new DuplicateKeyException("A record already exists for the key defined for type " + type.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("An error occurred promoting an object of type " + type.ToString() + ".", ex);
            }
            finally
            {
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }

            //Set the correct concurrency status
            if (graph.Concurrency != null) graph.Concurrency.Status = ConcurrencyStatus.Created;
        }

        //Update storage for the graph
        private void UpdateStorage(IPersistable graph)
        {
            Type type = Common.GetPersistType(graph);

            SqlFormatter formatter = (SqlFormatter) Formatter;

            formatter.WithInheritance = false;
            formatter.WithKeys = false;
            formatter.WithConcurrency = (graph.Concurrency != null);
            formatter.WithIdentity = true;
            formatter.Output = false;

            formatter.Concurrency = graph.Concurrency;

            //Update children by creating a list of procedure calls 
            _builder = new StringBuilder();
            UpdateStorageChildren(graph, type);

            //Get reflection details for top most item
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Get a reference to the context
            SqlContext context = (SqlContext)_context;

            //Try updates within a transaction
            System.Data.SqlClient.SqlConnection conn = null;
            System.Data.SqlClient.SqlCommand command = null;
            System.Data.SqlClient.SqlTransaction transaction = null;

            try
            {
                conn = context.GetConnection();

                //Begin transaction if multiple updates
                if (!cache.IsUnderlying) transaction = conn.BeginTransaction();

                command = conn.CreateCommand();
                command.Transaction = transaction;
                command.CommandText = _builder.ToString();
                int row = command.ExecuteNonQuery();

                //Make sure all rows were affected as command returns total rows affected
                if (row - 1 < cache.Rank) throw new ConcurrencyException("A concurrency error occurred for Type " + type.ToString());

                //Update streams
                if (cache.HasBinaryProperties) UpdateBinaryStorage(command, graph, type, formatter);

                //Commit the transaction
                if (!cache.IsUnderlying) transaction.Commit();
            }
            catch (Exception ex)
            {
                if (transaction != null) transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (transaction != null) transaction.Dispose();
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }

            //Update concurrency
            if (graph.Concurrency != null) graph.Concurrency.FromObject(graph);
        }

        private void UpdateStorageChildren(IPersistable graph, Type type)
        {
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Recurse so base types updated first
            if (!cache.IsUnderlying) UpdateStorageChildren(graph, cache.BaseType);

            //Get a reference to the context
            SqlContext context = (SqlContext)_context;

            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(type, ProcedureType.Update));
            _builder.Append("]");

            //Select concurrency procedure number
            if (graph.Concurrency == null) _builder.Append(";2");
            _builder.Append(" ");

            SqlFormatter formatter = (SqlFormatter)Formatter;
            formatter.Encode(graph, type);

            _builder.Append(" \r\n");
        }

        private void UpdateBinaryStorage(System.Data.SqlClient.SqlCommand command, IPersistable graph, Type type, SqlFormatter formatter)
        {
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Recurse to the bottom of the inheritance chain
            if (!cache.IsUnderlying) UpdateBinaryStorage(command, graph, cache.BaseType, formatter);

            foreach (PropertyInfo prop in cache.BinaryProperties)
            {
                UpdateBinaryStorageProperty(command, graph, type, formatter, prop, cache);
            }
        }

        private void UpdateBinaryStorageProperty(System.Data.SqlClient.SqlCommand command, IPersistable graph, Type type, SqlFormatter formatter, PropertyInfo prop, ReflectionCacheItem cache)
        {
            Stream stream = prop.GetValue(graph, null) as Stream;

            if (stream != null)
            {
                if (!stream.CanRead) throw new FormatException("The stream type provided for property " + prop.Name + " cannot be read.");
                if (!stream.CanSeek) throw new FormatException("The stream type provided for property " + prop.Name + " cannot be positioned.");

                stream.Position = 0;

                //Clear out old value
                _builder = new StringBuilder();
                _builder.Append("UPDATE [");
                _builder.Append(Common.GetTypeName(cache.PersistType, Context.Prefix));
                _builder.Append("] \r\n");
                _builder.Append("SET ");
                _builder.Append("[");
                _builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                _builder.Append("] = 0x \r\n");

                AppendBinaryWhere(cache, graph);

                _builder.Append(" \r\n");

                while (stream.Position < stream.Length)
                {
                    long offset = stream.Position;

                    //Create the builder and update
                    _builder.Append("UPDATE [");
                    _builder.Append(Common.GetTypeName(cache.PersistType, Context.Prefix));
                    _builder.Append("] \r\n");
                    _builder.Append("SET ");
                    _builder.Append("[");
                    _builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                    _builder.Append("] .WRITE(0x");

                    formatter.EncodeBinary(stream);

                    _builder.Append(",");
                    _builder.Append(offset.ToString());
                    _builder.Append(",");
                    _builder.Append(formatter.ChunkSize.ToString());
                    _builder.Append(") \r\n");

                    AppendBinaryWhere(cache, graph);

                    _builder.Append(" \r\n");

                    command.CommandText = _builder.ToString();
                    command.ExecuteNonQuery();

                    _builder = new StringBuilder();
                }
            }
        }

        private void AppendBinaryWhere(ReflectionCacheItem cache, IPersistable graph)
        {
            //Add the key where clause
            _builder.Append(" \r\nWHERE ");

            bool flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) _builder.Append(" AND ");

                _builder.Append("[");
                _builder.Append(SqlFormatter.GetPropertyName(prop, "."));
                _builder.Append("] = ");

                SqlFormatter.EncodeProperty(_builder, prop, prop.GetValue(graph, null));

                flag = true;
            }
        }

        //Returns storage for an IPersistable graph
        private void DeleteStorage(IPersistable graph)
        {
            Type type = Common.GetPersistType(graph);
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Get the stored procedure name and set up the context
            SqlContext context = (SqlContext)_context;

            _builder = new StringBuilder();

            //Build up the script to execute
            DeleteStorageChildren(graph, type, context);

            //Try updates within a transaction
            System.Data.SqlClient.SqlConnection conn = null;
            System.Data.SqlClient.SqlCommand command = null;
            System.Data.SqlClient.SqlTransaction transaction = null;

            try
            {
                conn = context.GetConnection();

                //Begin transaction if multiple updates
                if (!cache.IsUnderlying) transaction = conn.BeginTransaction();

                command = conn.CreateCommand();
                command.Transaction = transaction;
                command.CommandText = _builder.ToString();
                int row = command.ExecuteNonQuery();

                //Commit the transaction
                if (transaction != null) transaction.Commit();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                if (transaction != null) transaction.Rollback();

                if (ex.Number == 547)
                {
                    throw new ReferenceException("Storage for an object with type " + type.ToString() + " could not be deleted as it is referenced by another object.", ex);
                }
                else
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                if (transaction != null) transaction.Rollback();
                throw new CommandException("An error occurred deleting storage for type " + type.ToString() + ".", ex);
            }
            finally
            {
                if (transaction != null) transaction.Dispose();
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }

            //Set concurrency
            if (graph.Concurrency != null)
            {
                graph.Concurrency.Status = ConcurrencyStatus.Deleted;
                graph.Concurrency.Clear();
            }
        }

        private void DeleteStorageChildren(IPersistable graph, Type type, SqlContext context)
        {
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Set the stored procedure
            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(type, ProcedureType.Delete));
            _builder.Append("] ");

            //Add the key values as parameters
            bool flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) _builder.Append(",");

                _builder.Append("@");
                _builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                _builder.Append("=");
                SqlFormatter.EncodeProperty(_builder, prop, prop.GetValue(graph, null));
            }

            _builder.Append(" \r\n");

            if (!cache.IsUnderlying) DeleteStorageChildren(graph, cache.BaseType, context);
        }

        //Returns storage for an IPersistable graph
        private void DemoteStorage(IPersistable graph)
        {
            Type type = Common.GetPersistType(graph);
            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Get the stored procedure name and set up the context
            SqlContext context = (SqlContext)_context;

            _builder = new StringBuilder();

            //Set the stored procedure
            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(type, ProcedureType.Delete));
            _builder.Append("] ");

            //Add the key values as parameters
            bool flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) _builder.Append(",");

                _builder.Append("@");
                _builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                _builder.Append("=");
                SqlFormatter.EncodeProperty(_builder, prop, prop.GetValue(graph, null));
            }

            //Perform single delete
            System.Data.SqlClient.SqlConnection conn = null;
            System.Data.SqlClient.SqlCommand command = null;

            try
            {
                conn = context.GetConnection();

                command = conn.CreateCommand();
                command.CommandText = _builder.ToString();

                int row = command.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                if (ex.Number == 547)
                {
                    throw new ReferenceException("Storage for an object with type " + type.ToString() + " could not be demoted as it is referenced by another object.", ex);
                }
                else
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw new CommandException("An error occurred demoting storage for type " + type.ToString() + ".", ex);
            }
            finally
            {
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }

            //Set concurrency
            if (graph.Concurrency != null)
            {
                graph.Concurrency.Status = ConcurrencyStatus.Deleted;
                graph.Concurrency.Clear();
            }
        }

        //Returns storage for an IPersistable graph
        private bool GetStorageExists(IPersistable graph)
        {
            Type type = graph.GetType();

            //Get the stored procedure name and set up the context
            SqlContext context = (SqlContext)_context;

            //Set the context
            _builder = new StringBuilder();
            _builder.Append("EXEC [");
            _builder.Append(context.GetProcedure(type, ProcedureType.Exists));
            _builder.Append("] ");

            ReflectionCacheItem cache = Component.Instance.ReflectionCache.Lookup(type);

            //Add the key values as parameters
            bool flag = false;
            foreach (PropertyInfo prop in cache.GetKeyProperties())
            {
                if (flag) _builder.Append(",");

                _builder.Append("@");
                _builder.Append(SqlFormatter.GetPropertyName(prop, ""));
                _builder.Append("=");
                SqlFormatter.EncodeProperty(_builder, prop, prop.GetValue(graph, null));
            }

            //Open a connection and execute the procedure
            System.Data.SqlClient.SqlConnection conn = context.GetConnection();
            System.Data.SqlClient.SqlCommand command = conn.CreateCommand();
            command.CommandText = _builder.ToString();

            bool result;

            try
            {
                result = (command.ExecuteScalar() != null);
            }
            finally
            {
                if (command != null) command.Dispose();
                if (conn != null) conn.Dispose();
            }

            return result;
        }

        //Add a query and it's children to a dictionary, with a blank starting key
        private void AddQueryToDictionary(IQuery query, Dictionary<IQuery, QueryInformation> dictionary)
        {
            QueryInformation info;

            info.LastKey = "";
            info.Keys = new Dictionary<string, bool>();
            info.LastRelationship = null;
            info.Cache = Component.Instance.ReflectionCache.Lookup(query.ActivateType);

            dictionary.Add(query, info);

            //Recurse to add child query information to the dictionary
            if (query.SubQueries != null)
            {
                foreach (IQuery subQuery in query.SubQueries)
                {
                    AddQueryToDictionary(subQuery, dictionary);
                }
            }
        }

        //Set the relationships object on a result
        private void SetRelationships(IQuery query, IRelationships relationships)
        {
            if (query.Fill && query.Results != null) query.Results.Relationships = relationships;

            if (query.SubQueries != null && query.SubQueries.Count > 0)
            {
                foreach (IQuery subquery in query.SubQueries)
                {
                    SetRelationships(subquery, relationships);
                }
            }
        }

        #endregion

    }
}
